package com.py.credit.service.impl;

import com.py.credit.entity.LhjcMetric;
import com.py.credit.service.LhjcExcelReadService;
import com.py.credit.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;

@Service
public class LhjcExcelReadServiceImpl implements LhjcExcelReadService {

    private static final Logger LOG = LoggerFactory.getLogger(LhjcExcelReadServiceImpl.class);

    @Override
    public Sheet readExcelFileForSheet(String filePath) throws IOException {
        Workbook wb = null;
        FileInputStream fs = new FileInputStream(filePath);
        // Biff8EncryptionKey.setCurrentUserPassword("AAABBBABABA{");

        if (filePath.endsWith(".xls")) {
            wb = new HSSFWorkbook(fs);
        }
        if (filePath.endsWith(".xlsx")) {
            wb = new XSSFWorkbook(fs);
        }

        Sheet st = wb.getSheetAt(0);

        fs.close();

        return st;
    }

    @Override
    public List<Map<String, Object>> readExcelData(int start, int end, Sheet st, String batchId,
                                                   List<LhjcMetric> listMetrics, int cubeId) throws IOException {

        // 第一行为标题，不取
        Row row = null;
        int length = 0;
        Map<String, Object> map = null;
        Cell cell = null;
        String value = null;
        Date date = null;

        length = st.getLastRowNum();

        if (end > length) {
            end = length;
        }

        List<Map<String, Object>> listDataMap = new LinkedList<>();

        Pattern pattern1 = Pattern.compile("[0-9]{4}/[0-9]{1}/[0-9]{1}");
        Pattern pattern2 = Pattern.compile("[0-9]{4}/[0-9]{1}/[0-9]{2}");
        Pattern pattern3 = Pattern.compile("[0-9]{4}/[0-9]{2}/[0-9]{1}");
        Pattern pattern4 = Pattern.compile("[0-9]{4}/[0-9]{2}/[0-9]{2}");

        int begin = start + 3;

        for (int rowIndex = begin; rowIndex <= end; rowIndex++) {
            row = st.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            short lastCellNum = row.getLastCellNum();

            boolean emptyRow = true;

            for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {

                if (row.getCell(cellIndex, Row.RETURN_BLANK_AS_NULL) != null) {

                    row.getCell(cellIndex).setCellType(HSSFCell.CELL_TYPE_STRING);

                    if (!"".equals(row.getCell(cellIndex).getStringCellValue().trim())) {
                        {

                            emptyRow = false;
                            break;
                        }
                    }

                }
            }

            if (emptyRow) {

                continue;
            }
            map = new HashMap<String, Object>();
            for (int i = 0; i < listMetrics.size(); i++) {
                value = "";
                cell = row.getCell(i);
                if (cell != null) {
                    // 注意：一定要设成这个，否则可能会出现乱码
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue().trim();

                            if (StringUtils.isNotBlank(value)) {
                                try {
                                    if (pattern1.matcher(value).matches() || pattern2.matcher(value).matches()
                                            || pattern3.matcher(value).matches() || pattern4.matcher(value).matches()) {
                                        value = DateUtil.getInstance().parseDateStr(value, DateUtil.YYYY__MM__DD);
                                        break;
                                    }
                                } catch (Exception e) {
                                    LOG.error("日期类型无法转换" + value);
                                }
                            }
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                date = cell.getDateCellValue();
                                if (date != null) {
                                    value = DateUtil.getInstance().dateToStr(date, DateUtil.YYYY__MM__DD);
                                } else {
                                    value = "";
                                }
                            } else {
                                value = new DecimalFormat("#.####").format(cell.getNumericCellValue());
                            }
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            try {
                                if (!cell.getStringCellValue().equals("")) {
                                    value = cell.getStringCellValue();
                                } else {
                                    value = cell.getNumericCellValue() + "";
                                }
                            } catch (Exception e) {
                                value = "";
                                LOG.error(e.getMessage());
                                break;
                            }
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            value = "";
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        default:
                            value = "";
                    }
                }
                map.put(listMetrics.get(i).getMetricCode(), value);
                map.put("rowIndex", rowIndex + 1);
            }
            /* 把数据添加到List里面 */
            listDataMap.add(map);
            LOG.debug(map.toString());
        }

        return listDataMap;

    }

    @Override
    public Map<String, Object> readExcelFileHead(Sheet st, String batchId, List<LhjcMetric> listMetrics) {
        Map<String, Object> map = null;
        try {
            Row row = null;
            // 获取校验头
            row = st.getRow(0);
            for (int i = 0; i < listMetrics.size(); i++) {

                if (!row.getCell(i).toString().trim().equals(listMetrics.get(i).getMetricName())) {
                    LOG.debug("EXCEL第{}列表头:{}", i, row.getCell(i));
                    LOG.debug("数据库配置 第{}列表头:{}", i, listMetrics.get(i).getMetricName());
                    LOG.info("模板表头已被更改,请用规定的模板上传数据。");
                    map = new HashMap<String, Object>();
                    map.put("ERROR_MESS", "模板表头已被更改,请用规定的模板上传数据。");
                    break;
                }

            }
            return map;

        } catch (EncryptedDocumentException e) {
            map = new HashMap<String, Object>();
            map.put("ERROR_MESS", "模板保护密码已被更改");

        } catch (Exception e) {
            map = new HashMap<String, Object>();
            map.put("ERROR_MESS", "模板表头已被更改,请用规定的模板上传数据。");
        }
        return map;
    }

}
